package vn.edu.saigontech.ncms.web.report;

import java.io.IOException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;

import vn.edu.saigontech.ncms.model.info.Receipt;
import vn.edu.saigontech.ncms.service.ReceiptService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.ExcelUtil;
import vn.edu.saigontech.ncms.web.BaseAction;

@SuppressWarnings("serial")
public class AccountingReportAction extends BaseAction {
	
	private int month;
	private int year;
	
	private int toMonth;
	private int toYear;
	
	private String fromDate;
	private String toDate;
	
	/**
	 * EXPORT TUITION DETAILS LIST form date to date 
	 * */
	public String showTuitionDetailsListForm() {
		return SUCCESS;
	}
	
	public String exportTuitionDetailsList() {
		
		Date from = DateUtil.fromString(fromDate, DateUtil.DEFAULT_PATTERN);
		Date to = DateUtil.fromString(toDate, DateUtil.DEFAULT_PATTERN);
		
		List<Receipt> receipts = (List<Receipt>) ReceiptService.INSTANCE.findByDate(from, DateUtil.endOfDate(to));
		HSSFWorkbook wb = ExcelUtil.getTemplate("acc-tuition-details-list.xls");
			
		Map<String, CellStyle> styles = createStyles(wb);
		if (Checker.isNull(wb)) return NONE;

		/* BEGIN WRITING DATA */
		HSSFSheet sheet = wb.getSheetAt(0);
		
		HSSFRow row = sheet.getRow(1);
		HSSFCell cell = row.getCell(0);
		cell.setCellValue("Từ ngày " + DateUtil.toString(from, "dd/MM/yyyy") + " đến ngày " + DateUtil.toString(to, "dd/MM/yyyy"));
		
		int cRow = 5, startRow = 6;
		int lastRow = generateTuitionDetailsListCells(cRow, receipts, sheet, styles);
		
		row = sheet.createRow(lastRow);
		
		ExcelUtil.makeStringCell(row, 0, "Tổng cộng", styles, "cellRightBold");		
		ExcelUtil.makeStringCell(row, 1, "", styles, "cellRightBold");
		sheet.addMergedRegion(new CellRangeAddress(lastRow, lastRow, 0, 1));
		ExcelUtil.makeFormulaCell(row, 2, "sum(C"+startRow+":C"+lastRow+")", styles, "cellMoney");
		row.setHeightInPoints(25);
		
		sheet.createFreezePane(0, 5);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Tuition Summary " + 
				(DateUtil.getMonth(from)+1) + "." + DateUtil.getYear(from) + "-" + 
				(DateUtil.getMonth(to)+1) + "." + DateUtil.getYear(to) + ".xls\"");
		
		try {
			wb.write(RESPONSE().getOutputStream());
			this.addLoggingDetail(BaseAction.REPORT, "logging.acc.export.tuitionDetailsList", from, to);
		} catch (IOException e) {
			e.printStackTrace();
			return NONE;
		}
		return NONE;
	}
	
	private int generateTuitionDetailsListCells(int cRow, List<Receipt> details, HSSFSheet sheet, Map<String, CellStyle> styles) {
		for (int j = 0; j < details.size(); j++) {
			Receipt receipt = details.get(j);
			
			HSSFRow row = sheet.createRow(cRow++);
			ExcelUtil.makeNumericCell(row, 0, j+1, styles, "cell");
			ExcelUtil.makeStringCell(row, 1, receipt.getStudent().getFullName(), styles, "cellCenter");
			ExcelUtil.makeNumericCell(row, 2, receipt.getFee().getAmount().intValue(), styles, "cellMoney");			
		}
		return cRow;
	}
	
	/**
	 * EXPORT FEE SUMMARY from month/year to month/year 
	 * */
	public String showFeeSummaryForm() {
		year = toYear = DateUtil.getYear(DateUtil.newDate());
		month = 1;
		toMonth = DateUtil.getMonth(DateUtil.newDate())+1;
		return SUCCESS;
	}
	
	public String exportFeeSummary() {
		Date from = DateUtil.newDate(1, this.month, this.year);
		Date to = DateUtil.endOfMonth(DateUtil.newDate(1, this.toMonth, this.toYear));
		
		List<Receipt> receipts = (List<Receipt>) ReceiptService.INSTANCE.findByDate(from, DateUtil.endOfDate(to));
		HSSFWorkbook wb = ExcelUtil.getTemplate("acc-tuition-summary.xls");
			
		Map<String, CellStyle> styles = createStyles(wb);
		if (Checker.isNull(wb)) return NONE;

		/* BEGIN WRITING DATA */
		HSSFSheet sheet = wb.getSheetAt(0);
		
		HSSFRow row = sheet.getRow(1);
		HSSFCell cell = row.getCell(0);
		cell.setCellValue("Từ tháng " + DateUtil.toString(from, "MM/yyyy") + " đến tháng " + DateUtil.toString(to, "MM/yyyy"));
		
		int cRow = 5;
		int lastRow = generateTuitionSummaryCells(cRow, receipts, sheet, styles);
		
		row = sheet.createRow(lastRow);
		
		ExcelUtil.makeStringCell(row, 0, "Tổng cộng", styles, "cellRightBold");		
		ExcelUtil.makeFormulaCell(row, 1, "sum(B"+cRow+":B"+lastRow+")", styles, "cellRightBold");
		ExcelUtil.makeFormulaCell(row, 2, "sum(C"+cRow+":C"+lastRow+")", styles, "cellRightBold");
		ExcelUtil.makeFormulaCell(row, 3, "sum(D"+cRow+":D"+lastRow+")", styles, "cellRightBold");
		ExcelUtil.makeFormulaCell(row, 4, "sum(E"+cRow+":E"+lastRow+")", styles, "cellRightBold");
		ExcelUtil.makeFormulaCell(row, 5, "sum(F"+cRow+":F"+lastRow+")", styles, "cellRightBold");
		ExcelUtil.makeFormulaCell(row, 6, "sum(G"+cRow+":G"+lastRow+")", styles, "cellRightBold");
		row.setHeightInPoints(25);
		
		sheet.createFreezePane(0, 5);
		
		RESPONSE().setContentType("application/vnd.ms-excel");
		RESPONSE().setHeader("Content-disposition","attachment; filename=\""+ "Tuition Summary " + 
				(DateUtil.getMonth(from)+1) + "." + DateUtil.getYear(from) + "-" + 
				(DateUtil.getMonth(to)+1) + "." + DateUtil.getYear(to) + ".xls\"");
		
		try {
			wb.write(RESPONSE().getOutputStream());
			this.addLoggingDetail(BaseAction.REPORT, "logging.acc.export.tuitionSummary", from, to);
		} catch (IOException e) {
			e.printStackTrace();
			return NONE;
		}
		return NONE;
	}
	
	private int generateTuitionSummaryCells(int cRow, List<Receipt> details, HSSFSheet sheet, Map<String, CellStyle> styles) {
		int nMonths = DateUtil.diffMonth(year, month, toYear, toMonth);
		Date startDate = DateUtil.newDate(1, month, year);
		for (int i = 0; i < nMonths; i++) {
			int nEnglishStudents = 0;
			//int nComputerStudents = 0;
			double englishTuition = 0;
			//double computerTuition = 0;
			
			for (int j = 0; j < details.size(); j++) {
				Receipt receipt = details.get(j);
				if (DateUtil.inSameMonth(receipt.getDate(), startDate)) {
					nEnglishStudents++;
					//nComputerStudents++;
					englishTuition += receipt.getFee().getAmount();
					//computerTuition += receipt.getFee().getAmount();
				}
			}
			
			HSSFRow row = sheet.createRow(cRow++);
			String datePattern = "MM/yyyy";
			
			ExcelUtil.makeStringCell(row, 0, DateUtil.toString(startDate, datePattern), styles, "cellCenter");
			ExcelUtil.makeNumericCell(row, 1, nEnglishStudents, styles, "cellCenter");
			ExcelUtil.makeNumericCell(row, 2, 0, styles, "cellCenter");
			ExcelUtil.makeFormulaCell(row, 3, "B"+cRow + "-C" + cRow, styles, "cellCenter");			
			
			ExcelUtil.makeNumericCell(row, 4, (int)englishTuition, styles, "cellMoney");
			ExcelUtil.makeNumericCell(row, 5, 0, styles, "cellMoney");
			ExcelUtil.makeFormulaCell(row, 6, "E"+cRow + "-F" + cRow, styles, "cellMoney");
			
			startDate = DateUtil.addMonth(startDate, 1);
		}
		return cRow;
	}
	
	/**
     * Create a library of cell styles
     */
    private static Map<String, CellStyle> createStyles(Workbook wb){
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style;
        
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short)13);
        titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(titleFont);
        styles.put("title", style);
        
        Font subtitleFont = wb.createFont();
        subtitleFont.setFontHeightInPoints((short)12);
        subtitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setFont(subtitleFont);
        styles.put("subtitle", style);

        Font cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        styles.put("cell", style);

        cellFont = wb.createFont();
        cellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cellFont);
        DataFormat format = wb.createDataFormat();
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellMoney", style);
        
        Font cCellFont = wb.createFont();
        cCellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cCellFont);
        styles.put("cellCenter", style);
        
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(cCellFont);
        styles.put("cellRight", style);
        
        Font bold = wb.createFont();
        bold.setBoldweight(Font.BOLDWEIGHT_BOLD);
        bold.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFont(bold);
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellRightBold", style);
        
        cCellFont.setFontHeightInPoints((short)10);
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_LEFT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(cCellFont);
        styles.put("cellGreen", style);
        
        style = wb.createCellStyle();
        style.setAlignment(CellStyle.ALIGN_RIGHT);
        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        style.setBorderRight(CellStyle.BORDER_THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(CellStyle.BORDER_THIN);
        style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(CellStyle.BORDER_THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderBottom(CellStyle.BORDER_THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setFillForegroundColor(IndexedColors.RED.getIndex());
        style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        style.setFont(bold);
        style.setDataFormat(format.getFormat("#,##0"));
        styles.put("cellRed", style);
        
        return styles;
    }
	
	public int getMonth() { return month; }
	public void setMonth(int month) { this.month = month; }

	public int getYear() { return year; }
	public void setYear(int year) { this.year = year; }
	
	public int getToMonth() { return toMonth; }
	public void setToMonth(int toMonth) { this.toMonth = toMonth; }

	public int getToYear() { return toYear; }
	public void setToYear(int toYear) { this.toYear = toYear; }
	

	public String getFromDate() { return fromDate; }
	public void setFromDate(String fromDate) { this.fromDate = fromDate; }

	public String getToDate() { return toDate; }
	public void setToDate(String toDate) { this.toDate = toDate; }
}